using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Author:  Joe Morel
/// 
/// Microsoft Corporation (c) 2006
/// All Rights Reserved.
/// 
/// The Tagging class incorporates the background logic for webservices that allow a user to "tag" a given threadID for
/// the Microsoft forums, and query back on those threads and find all of the threads tagged with a particular tag
/// </summary>
/// 
public class Tagging
{
	public Tagging()
	{
		//
		// TODO: Add constructor logic here
		//
	}


    //This method returns an array of tags for a given ThreadID
    //It is independent of the user that submitted the string
    public string[] GetTagsForThread(int ThreadID)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT Tags.Tag FROM Tags INNER JOIN ThreadTag ON Tags.TagID = ThreadTag.TagID WHERE (ThreadTag.ThreadID = " + ThreadID.ToString() + ")", connection);
        SqlDataReader reader = command.ExecuteReader();
        System.Collections.Generic.List<string> list = new System.Collections.Generic.List<string>();
        while(reader.Read())
            list.Add(reader.GetString(0));
        connection.Close();
        return list.ToArray();
    }

    public int[] GetThreadIDsbyTag(string tag)
    {
        string TagID = GetTagID(tag);
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT ThreadTag.ThreadID FROM Tags INNER JOIN ThreadTag ON Tags.TagID = ThreadTag.TagID WHERE (Tags.TagID = '" + TagID + "')", connection);
        SqlDataReader reader = command.ExecuteReader();

        System.Collections.Generic.List<int> list = new System.Collections.Generic.List<int>();
        while(reader.Read())
        {
            list.Add(reader.GetInt32(0));
        }
        connection.Close();
        return list.ToArray();
    }

    public string GetSubjectbyThreadID(int threadID)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT Subject FROM Threads WHERE ThreadID = " + threadID.ToString(), connection);
        SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
            return reader.GetString(0);
        else
            return String.Empty;
    }

    //This method adds a tag to the given thread
    public void AddTag(int ThreadID, string tag, string subject)
    {
        if (IsTaggedAlready(tag, ThreadID))
        {
            string tagID = GetTagID(tag);
            SqlConnection connection;
            SqlCommand command;
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
            connection.Open();
            //if the tagID was not found, create a new tag record for it
            if (tagID == null)
            {
                command = new SqlCommand("INSERT INTO Tags VALUES (NEWID(),'" + tag + "')", connection);
                command.ExecuteNonQuery();
                tagID = GetTagID(tag);
            }

            command = new SqlCommand("INSERT INTO ThreadTag VALUES (NEWID(),'" + tagID + "'," + ThreadID.ToString() + ")", connection);
            command.ExecuteNonQuery();
            command = new SqlCommand("INSERT INTO Threads VALUES (" + ThreadID.ToString() + ",'None','" + subject + "')", connection);
            command.ExecuteNonQuery();
            connection.Close();
        }
        else
            throw new System.ArgumentException("The given thread has already been tagged with that tag.", "tag", null);
    }

    //this method returns 0 if the given string isn't set as a tag, and returns the int of the TagID in the database
    //if it does exist in the database
    private string GetTagID(string tag)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        try
        {
            connection.Open();
        }
            //What do you do if a connection couldn't be opened?  For now, just return an empty string for this method, because
            //this method is not going to be publicly exposed through a webmethod

        catch
        {
            return String.Empty;
        }

        SqlCommand command = new SqlCommand("SELECT TagID FROM Tags WHERE (Tag = '" + tag + "')", connection);
        SqlDataReader reader = command.ExecuteReader();

        if (!reader.Read())
        {
            reader.Close();
            connection.Close();
            return null;
        }
        else
        {
            Guid temp = reader.GetGuid(0);
            reader.Close();
            connection.Close();
            return temp.ToString();
        }
    }

    /// <summary>
    /// This method will determine whether or not a thread has already been tagged with a given tag
    /// </summary>
    /// <param name="tag">String for the tag that we are checking for</param>
    /// <param name="postID">PostID of the thread we are checking</param>
    /// <returns></returns>
    private bool IsTaggedAlready(string tag, int postID)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT Threads.Subject FROM Tags INNER JOIN ThreadTag ON Tags.TagID = ThreadTag.TagID CROSS JOIN Threads WHERE (Tags.Tag = '" + tag + "') AND (Threads.ThreadID = " + postID.ToString() + ")", connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            if (reader.GetString(0) != null)
                return true;
            else
                return false;
        }
        return false;
    }

    public string[] GetTopTags()
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSModerators"].ConnectionString);
        try
        {
            connection.Open();
        }
        //What do you do if a connection couldn't be opened?  For now, just return an empty string for this method, because
        //this method is not going to be publicly exposed through a webmethod

        catch
        {
            return null;
        }

        SqlCommand command = new SqlCommand("SELECT TOP (10) Tags.Tag, COUNT(ThreadTag.TagID) AS Totals FROM Tags INNER JOIN ThreadTag ON Tags.TagID = ThreadTag.TagID GROUP BY ThreadTag.TagID, Tags.Tag ORDER BY Totals DESC", connection);
        SqlDataReader reader = command.ExecuteReader();
        List<string> topTags = new List<string>();
        while (reader.Read())
            topTags.Add(reader.GetString(0));
        return topTags.ToArray();
    }
}
